# -*- coding: utf-8 -*-
# Part of Odoo. See LICENSE file for full copyright and licensing details.

from odoo import tools
from odoo import api, fields, models


class StockReport(models.Model):
    _name = "out.return.stock"
    _description = "Delivery Order Detail Report"
    _auto = False
    # _rec_name = 'name'
    _order = 'picking_id desc'

    # name = fields.Char('Order Reference', readonly=True)
    out_date = fields.Datetime(u'出货日期', readonly=True)
    product_id = fields.Many2one('product.product', u'产品', readonly=True)
    project_id = fields.Many2one('account.analytic.account', u'分析账户', readonly=True)
    partner_id = fields.Many2one('res.partner', u'客户名称', readonly=True)
    order_id = fields.Many2one('sale.order', u'SO单号', readonly=True)
    quantity = fields.Float(u'销售订单数量', readonly=True)
    picking_id = fields.Many2one('stock.picking', u'出货单号', readonly=True)
    out_qty = fields.Float(u'出货数量', readonly=True)
    out_amt = fields.Float(u'出货金额(不含税)', readonly=True)
    out_amt_taxed = fields.Float(u'出货金额(含税)', readonly=True)
    rtn_date = fields.Datetime(u'退货日期', readonly=True)
    rtn_no = fields.Char(u'退货单号', readonly=True)
    rtn_qty = fields.Float(u'退货数量', readonly=True)
    user_id = fields.Many2one('res.users', u'业务员', readonly=True)
    team_id = fields.Many2one('crm.team', u'销售团队', readonly=True)

    @api.model_cr
    def init(self):
        tools.drop_view_if_exists(self.env.cr, self._table)
        self.env.cr.execute("""CREATE or REPLACE VIEW %s as (
SELECT id,
       name,
       user_id,
       team_id,
       order_id,
       partner_id,
       project_id,
       product_id,
       quantity,
       picking_id,
       out_date,
       out_qty,
       out_amt,
       out_amt_taxed,
       min(rtn_date)                                            AS rtn_date,
       sum(rtn_qty)                                             AS rtn_qty,
       array_to_string(array_agg(rtn_no ORDER BY rtn_no), ', ') as rtn_no
FROM (SELECT outmove.id                                                     AS id,
             sp.name                                                        AS name,
             so.user_id                                                     AS user_id,
             so.team_id                                                     AS team_id,
             so.partner_id                                                  AS partner_id,
             so.project_id                                                  AS project_id,
             so.id                                                          AS order_id,
             outmove.product_id                                             AS product_id,
             sol.product_uom_qty                                            AS quantity,
             sp.id                                                          AS picking_id,
             coalesce(sp.force_date, sp.date_done)                          AS out_date,
             outmove.product_qty                                            AS out_qty,
             sol.price_subtotal / sol.product_uom_qty * outmove.product_qty AS out_amt,
             sol.price_total / sol.product_uom_qty * outmove.product_qty    AS out_amt_taxed,
             rtnmove.force_date                                             AS rtn_date,
             rtnsp.name                                                     AS rtn_no,
             rtnmove.product_qty                                            AS rtn_qty
      FROM stock_move outmove
             INNER JOIN stock_picking sp ON outmove.picking_id = sp.id
             INNER JOIN procurement_order po ON outmove.procurement_id = po.id
             INNER JOIN sale_order_line sol ON po.sale_line_id = sol.id
             INNER JOIN sale_order so ON sol.order_id = so.id
             LEFT JOIN stock_move rtnmove
               ON rtnmove.origin_returned_move_id = outmove.id AND outmove.product_id = rtnmove.product_id
             LEFT JOIN stock_picking rtnsp ON rtnsp.id = rtnmove.picking_id
      WHERE coalesce(sp.state, 'NULL') = 'done'
        AND coalesce(rtnmove.state, 'NULL') <> 'cancel') A
GROUP BY order_id, product_id, quantity, picking_id, out_date, out_qty,
         out_amt, out_amt_taxed, name, id, partner_id, project_id, user_id, team_id
            )""" % self._table)
